from re import T, split, template
from openpyxl import load_workbook
import json
from excel_info import DataType, TitleFlag, TitleInfo

def excel_to_json(excel_file,json_file, star_line):
    wb=load_workbook(excel_file, data_only=True)#读取excel文件
    data={}
    primaryTitleInfos={}
    for sheet in wb.sheetnames:
        sheet_spl=sheet.split('<')
        sheet_real_name=sheet_spl[len(sheet_spl)-1]
        resultItem = []
        titleInfos=row_to_titleInfos(list(wb[sheet].rows)[star_line])
        for titleInfo in titleInfos:#记录主键
            if titleInfo.isPrimaryKey:
                primaryTitleInfos[sheet_real_name]=titleInfo
        for i, row in enumerate(wb[sheet]):#获取表的每一行数据
            if i>star_line:
                resultItem.append(row_work_content(row, titleInfos))
        data[sheet_real_name]=resultItem
    relation=sheet_relation(wb)
    # print(relation)
    result={}
    merge_data_enter(data, relation, result, primaryTitleInfos)
    # print(result)
    #覆盖写入json文件
    with open(json_file, mode='w', encoding='utf-8') as jf:
        json.dump(result, jf, indent=2, sort_keys=True, ensure_ascii=False)

def sheet_relation(wb):
    '''梳理主从关系'''
    relation={}
    for sheet in wb.sheetnames:
        tier=sheet.split('<')
        if len(tier)==1:
            relation[tier[0]]={}
        if len(tier)==2:
            search_tier(tier[1], tier[0], relation)
    return relation;

def search_tier(item, before, relation):
    if len(relation)<=0:
        return
    if before not in relation:
        for tier in relation.values():
            search_tier(item, before, tier)
    else:
        if item not in before:
            relation[before][item]={}

def row_to_titleInfos(row):
    '''整理excel数据'''
    titleInfos=[]
    isExistPrimary=False
    for cell in row:
        titleStr=cell.value
        titleInfo = TitleInfo()
        if titleStr[0]==TitleFlag.ignore:#判断忽略
            titleInfo.isIgnore=True
            titleStr=titleStr.lstrip(TitleFlag.ignore)
        if titleStr[0]==TitleFlag.prime:#判断主键
            isExistPrimary=True
            titleInfo.isPrimaryKey=True
            titleStr=titleStr.lstrip(TitleFlag.prime)
        spl=titleStr.split('#')
        titleInfo.name=spl[0]
        if titleInfo.isPrimaryKey:#设置主键
            titleInfo.primaryKey=titleInfo.name
        if len(spl)==2:#目前只识别特殊类型
            if spl[1]==DataType.LIST:
                titleInfo.dataType=DataType.LIST
            elif spl[1]==DataType.DICT:
                titleInfo.dataType=DataType.DICT
        titleInfos.append(titleInfo)
    if isExistPrimary==False and len(titleInfos)>0:#默认第一列为主键
        titleInfos[0].isPrimaryKey=True
    return titleInfos

def row_work_content(row, titleInfos):
    item={}
    for i, cell in enumerate(row):
        if titleInfos[i].isIgnore or cell.value is None:
            continue
        if titleInfos[i].dataType==DataType.LIST or titleInfos[i].dataType==DataType.DICT:
            item[titleInfos[i].name]=eval(cell.value)
        else:
            item[titleInfos[i].name]=cell.value
    return item

def merge_data_enter(data, relation, result, primaryTitleInfos,startFlag=0):
    '''将从表数据合并到主表'''
    if len(relation)<=0:
        return
    for item in relation:
        if startFlag==0:
            result[item]=data[item]
        else:
            for findItem in data[item]:
                if result[primaryTitleInfos[item].primaryKey]==findItem[primaryTitleInfos[item].primaryKey]:
                    result[item]=findItem
                    break
        if isinstance(result[item], list):
            for nextItem in result[item]:
                merge_data_enter(data, relation[item], nextItem, primaryTitleInfos, 1)
        elif isinstance(result[item], dict):
            merge_data_enter(data, relation[item], result[item], primaryTitleInfos, 1)


if __name__=='__main__':
    excelFile=input('excel:')
    jsonFile=input('json:')
    excel_to_json(excelFile, jsonFile, 1)#调用函数，传入参数